package df.bean.interfacefile;

import df.bean.db.conn.DBConn;
import df.bean.db.conn.DBConnection;
import df.bean.obj.util.JDate;
import java.sql.ResultSet;

public class InterfaceExportGLBean {
    private DBConnection dbconn;
    private DBConn conn;
    private String YYYY;
    private String MM;
    private String Hospital_code;
    private String TypeData;
    private int count_insert;

    public InterfaceExportGLBean(String yyyy, String mm, String hospital_code){
        conn = new DBConn();
        YYYY = yyyy;
        MM = mm;
        Hospital_code = hospital_code;
        try{
            conn.setStatement();
        }catch(Exception e){
            System.out.println(e);
        }
    }

    private String accuDebitDfOk(){
        return "INSERT INTO TEMP_GL(" +
	       		"HOSPITAL_CODE, "+
        		"ACCOUNTING_DATE, "+
                "ACCOUNT_CODE, " +
                "FIRST_DEPARTMENT_CODE, "+
                "FIRST_LOCATION_CODE," +
                "PATIENT_LOCATION_CODE, " +
                "RECEIPT_LOCATION_CODE, " +
        		"ADMISSION_TYPE_CODE, " +
        		"ACTIVITY, "+
                "NATIONALITY_CODE," +
                "CURRENCY_CODE, " +
                "AMOUNT_AFT_DISCOUNT, " +
                "AMOUNT_SIGN, " +
                "ACCOUNTING_TEMPLATE, " +
                "LINE_NO, "+
                "MM, " +
                "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.INVOICE_DATE, " +
                "OI.ACCOUNT_CODE, "+
                "CASE WHEN (T.FIRST_DEPARTMENT_CODE = '' OR T.FIRST_DEPARTMENT_CODE IS NULL) " +
            		"THEN T.DOCTOR_DEPARTMENT_CODE ELSE T.FIRST_DEPARTMENT_CODE " +
            		"END AS FIRST_DEPARTMENT_CODE, " +
                "T.FIRST_LOCATION_CODE, " +
                "T.PATIENT_LOCATION_CODE, " +
                "T.RECEIPT_LOCATION_CODE, " +
                "T.ADMISSION_TYPE_CODE, " +
                "'DF', "+ //ACTIVITY
                "T.NATIONALITY_CODE, "+
                "'THB',"+
                "T.AMOUNT_AFT_DISCOUNT, "+
	            "'+', 'DFACCRUE', " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_DAILY T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN ORDER_ITEM OI ON (T.ORDER_ITEM_CODE = OI.CODE AND T.HOSPITAL_CODE = OI.HOSPITAL_CODE) "+
                "WHERE (T.BATCH_NO = '' OR T.BATCH_NO IS NULL) "+
                "AND T.INVOICE_TYPE <> 'ORDER' " +
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.IS_PAID <> 'N' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' AND T.ORDER_ITEM_ACTIVE = '1'";
    }
    private String accuCreditDfOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE," +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE," +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.INVOICE_DATE, " +
                "'231014', "+
                "CASE WHEN (T.FIRST_DEPARTMENT_CODE = '' OR T.FIRST_DEPARTMENT_CODE IS NULL) " +
            		"THEN T.DOCTOR_DEPARTMENT_CODE ELSE T.FIRST_DEPARTMENT_CODE " +
            		"END AS FIRST_DEPARTMENT_CODE, " +
                "T.FIRST_LOCATION_CODE, " +
                "T.PATIENT_LOCATION_CODE, " +
                "T.RECEIPT_LOCATION_CODE, " +
                "T.ADMISSION_TYPE_CODE, " +
                "'DF', "+
                "T.NATIONALITY_CODE, "+
                "'THB', "+
                "T.AMOUNT_AFT_DISCOUNT, "+
	            "'-', 'DFACCRUE', " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_DAILY T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN ORDER_ITEM OI ON (T.ORDER_ITEM_CODE = OI.CODE AND T.HOSPITAL_CODE = OI.HOSPITAL_CODE) "+
                "WHERE (T.BATCH_NO = '' OR T.BATCH_NO IS NULL) "+
                "AND T.INVOICE_TYPE <> 'ORDER' " +
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.IS_PAID <> 'N' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' AND T.ORDER_ITEM_ACTIVE = '1'";
    }
    private String accuDebitSharingOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE," +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE," +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.INVOICE_DATE, " +
                "'231014', "+
                "CASE WHEN (T.FIRST_DEPARTMENT_CODE = '' OR T.FIRST_DEPARTMENT_CODE IS NULL) " +
            		"THEN T.DOCTOR_DEPARTMENT_CODE ELSE T.FIRST_DEPARTMENT_CODE " +
            		"END AS FIRST_DEPARTMENT_CODE, " +
                "T.FIRST_LOCATION_CODE, " +
                "T.PATIENT_LOCATION_CODE, " +
                "T.RECEIPT_LOCATION_CODE, " +
                "T.ADMISSION_TYPE_CODE, " +
                "'DF', "+
                "T.NATIONALITY_CODE, "+
                "'THB', "+
                "T.AMOUNT_AFT_DISCOUNT-T.DR_AMT, "+
	            "'+', 'DFACCRUE', " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_DAILY T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN ORDER_ITEM OI ON (T.ORDER_ITEM_CODE = OI.CODE AND T.HOSPITAL_CODE = OI.HOSPITAL_CODE) "+
                "WHERE (T.BATCH_NO = '' OR T.BATCH_NO IS NULL) "+
                "AND T.INVOICE_TYPE <> 'ORDER' " +
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.IS_PAID <> 'N' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' AND T.ORDER_ITEM_ACTIVE = '1'";
    }
    private String accuCreditSharingOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE," +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE," +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.INVOICE_DATE, " +
                "SUBSTRING(OI.ACCOUNT_CODE,1,3)+'2'+SUBSTRING(OI.ACCOUNT_CODE,5,6) AS ACCOUNT_CODE, "+
                "CASE WHEN (T.FIRST_DEPARTMENT_CODE = '' OR T.FIRST_DEPARTMENT_CODE IS NULL) " +
            		"THEN T.DOCTOR_DEPARTMENT_CODE ELSE T.FIRST_DEPARTMENT_CODE " +
            		"END AS FIRST_DEPARTMENT_CODE, " +
                "T.FIRST_LOCATION_CODE, " +
                "T.PATIENT_LOCATION_CODE, " +
                "T.RECEIPT_LOCATION_CODE, " +
                "T.ADMISSION_TYPE_CODE, " +
                "'DF', "+
                "T.NATIONALITY_CODE, "+
                "'THB', "+
                "T.AMOUNT_AFT_DISCOUNT-T.DR_AMT, "+
	            "'-', 'DFACCRUE', " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_DAILY T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN ORDER_ITEM OI ON (T.ORDER_ITEM_CODE = OI.CODE AND T.HOSPITAL_CODE = OI.HOSPITAL_CODE) "+
                "WHERE (T.BATCH_NO = '' OR T.BATCH_NO IS NULL) "+
                "AND T.INVOICE_TYPE <> 'ORDER' " +
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.IS_PAID <> 'N' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' AND T.ORDER_ITEM_ACTIVE = '1'";
    }
    private String accuDebitAbsorbDailyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.START_DATE, " +
                "'501301', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.HP402_ABSORB_AMOUNT+T.DF406_HOLD_AMOUNT, "+
	            "'+', 'DFACCRUE', " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'DLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1'";
    }
    private String accuCreditAbsorbDailyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.START_DATE, " +
                "'231014', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.HP402_ABSORB_AMOUNT+T.DF406_HOLD_AMOUNT, "+
	            "'-', 'DFACCRUE', " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'DLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1'";
    }
    private String accuDebitAbsorbMonthlyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT DISTINCT " +
                "T.HOSPITAL_CODE, " +
                "T.GUARANTEE_CODE, "+
                //"T.START_DATE, " +
                "'501301', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.HP402_ABSORB_AMOUNT+T.DF406_HOLD_AMOUNT, "+
	            "'+', 'DFACCRUE', " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'MLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1'";
    }
    private String accuCreditAbsorbMonthlyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT DISTINCT " +
                "T.HOSPITAL_CODE, " +
                "T.GUARANTEE_CODE, "+
                //"T.START_DATE, " +
                "'231014', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.HP402_ABSORB_AMOUNT+T.DF406_HOLD_AMOUNT, "+
	            "'-', 'DFACCRUE', " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'MLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1'";
    }
    private String accuDebitFixDailyOk(){
            return "INSERT INTO TEMP_GL(" +
    				"HOSPITAL_CODE, "+
    				"ACCOUNTING_DATE, "+
    			    "ACCOUNT_CODE, " +
    			    "FIRST_DEPARTMENT_CODE, "+
    			    "FIRST_LOCATION_CODE, " +
    			    "PATIENT_LOCATION_CODE, " +
    			    "RECEIPT_LOCATION_CODE, " +
    				"ADMISSION_TYPE_CODE, " +
    				"ACTIVITY, "+
    			    "NATIONALITY_CODE, " +
    			    "CURRENCY_CODE, " +
    			    "AMOUNT_AFT_DISCOUNT, " +
    			    "AMOUNT_SIGN, " +
    			    "ACCOUNTING_TEMPLATE, " +
    			    "LINE_NO, "+
    			    "MM, " +
    			    "YYYY) " +
                    
                    "SELECT " +
                    "T.HOSPITAL_CODE, " +
                    "T.START_DATE, " +
                    "'501302', "+
                    "D.DEPARTMENT_CODE, " +
                	"'', " + //FIRST LOCATION
                    "'', " + //PATIENT LOCATION
                    "'', " + //RECEIPT LOCATION
                    "'', " + //ADMISSION_TYPE
                    "'GR', "+ //ACTIVITY
                    "'', "+ //NATIONALITY
                    "'THB', "+ //CURRENCY
                    "T.GUARANTEE_FIX_AMOUNT+T.GUARANTEE_EXCLUDE_AMOUNT, "+
    	            "'+', 'DFACCRUE', " +
    	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                    "'"+this.MM+"', " +
                    "'"+this.YYYY+"' " +
                    
                    "FROM STP_GUARANTEE T " +
                    "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                    "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                    "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'DLY' " +
                    "AND D.ACTIVE = '1' AND T.ACTIVE = '1'";

    }
    private String accuCreditFixDailyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.START_DATE, " +
                "'231014', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.GUARANTEE_FIX_AMOUNT+T.GUARANTEE_EXCLUDE_AMOUNT, "+
	            "'-', 'DFACCRUE', " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'DLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1'";

}
    private String accuDebitFixMonthlyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.START_DATE, " +
                "'501302', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.GUARANTEE_FIX_AMOUNT+T.GUARANTEE_EXCLUDE_AMOUNT, "+
	            "'+', 'DFACCRUE', " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'MLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1'";
    }
    private String accuCreditFixMonthlyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.START_DATE, " +
                "'231014', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.GUARANTEE_FIX_AMOUNT+T.GUARANTEE_EXCLUDE_AMOUNT, "+
	            "'-', 'DFACCRUE', " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'MLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1'";
    }
    private String accuDebitExpenseOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.DOC_DATE, " +
                "'231014', "+
                "T.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'AJ', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.AMOUNT, "+
	            "'+', 'DFACCRUE', " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_EXPENSE_DETAIL T " +
                "LEFT OUTER JOIN EXPENSE E ON (T.EXPENSE_CODE = E.CODE AND T.HOSPITAL_CODE = E.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' AND T.EXPENSE_SIGN = '-1' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND E.GL_INTERFACE = 'Y' AND E.ADJUST_TYPE NOT IN ('DR','HP') " +
                "AND E.ACTIVE = '1'";
    }
    private String accuCreditExpenseOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.DOC_DATE, " +
                "T.EXPENSE_ACCOUNT_CODE, "+
                "T.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'AJ', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.AMOUNT, "+
	            "'-', 'DFACCRUE', " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_EXPENSE_DETAIL T " +
                "LEFT OUTER JOIN EXPENSE E ON (T.EXPENSE_CODE = E.CODE AND T.HOSPITAL_CODE = E.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' AND T.EXPENSE_SIGN = '-1' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND E.GL_INTERFACE = 'Y' AND E.ADJUST_TYPE NOT IN ('DR','HP') " +
                "AND E.ACTIVE = '1'";
    }
    private String accuDebitAddOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.DOC_DATE, " +
                "T.EXPENSE_ACCOUNT_CODE, "+
                "T.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'AJ', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.AMOUNT, "+
	            "'+', 'DFACCRUE', " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_EXPENSE_DETAIL T " +
                "LEFT OUTER JOIN EXPENSE E ON (T.EXPENSE_CODE = E.CODE AND T.HOSPITAL_CODE = E.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' AND T.EXPENSE_SIGN = '1' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND E.GL_INTERFACE = 'Y' AND E.ADJUST_TYPE NOT IN ('DR','HP') " +
                "AND E.ACTIVE = '1'";
    }
    private String accuCreditAddOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.DOC_DATE, " +
                "'231014', "+
                "T.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'AJ', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.AMOUNT, "+
	            "'-', 'DFACCRUE', " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_EXPENSE_DETAIL T " +
                "LEFT OUTER JOIN EXPENSE E ON (T.EXPENSE_CODE = E.CODE AND T.HOSPITAL_CODE = E.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' AND T.EXPENSE_SIGN = '1' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND E.GL_INTERFACE = 'Y' AND E.ADJUST_TYPE NOT IN ('DR','HP') " +
                "AND E.ACTIVE = '1'";
    }

    private String payDebitDfOk(){
        return "INSERT INTO TEMP_GL(" +
	       		"HOSPITAL_CODE, "+
        		"ACCOUNTING_DATE, "+
                "ACCOUNT_CODE, " +
                "FIRST_DEPARTMENT_CODE, "+
                "FIRST_LOCATION_CODE," +
                "PATIENT_LOCATION_CODE, " +
                "RECEIPT_LOCATION_CODE, " +
        		"ADMISSION_TYPE_CODE, " +
        		"ACTIVITY, "+
                "NATIONALITY_CODE," +
                "CURRENCY_CODE, " +
                "AMOUNT_AFT_DISCOUNT, " +
                "AMOUNT_SIGN, " +
                "ACCOUNTING_TEMPLATE, " +
                "LINE_NO, "+
                "MM, " +
                "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.INVOICE_DATE, " +
                "OI.ACCOUNT_CODE, "+
                "CASE WHEN (T.FIRST_DEPARTMENT_CODE = '' OR T.FIRST_DEPARTMENT_CODE IS NULL) " +
            		"THEN T.DOCTOR_DEPARTMENT_CODE ELSE T.FIRST_DEPARTMENT_CODE " +
            		"END AS FIRST_DEPARTMENT_CODE, " +
                "T.FIRST_LOCATION_CODE, " +
                "T.PATIENT_LOCATION_CODE, " +
                "T.RECEIPT_LOCATION_CODE, " +
                "T.ADMISSION_TYPE_CODE, " +
                "'DF', "+
                "T.NATIONALITY_CODE, "+
                "'THB', "+
                "T.AMOUNT_AFT_DISCOUNT, "+
                "'+', "+
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
        			"THEN 'DFCHECK' ELSE 'DFTRANS' " +
        		"END AS ACCOUNTING_TEMPLATE, " +
	            //"'DFACCRUE', " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_DAILY T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN ORDER_ITEM OI ON (T.ORDER_ITEM_CODE = OI.CODE AND T.HOSPITAL_CODE = OI.HOSPITAL_CODE) "+
        
		        "WHERE T.YYYY='"+this.YYYY+"' AND T.MM='"+this.MM+"' "+
		        "AND T.INVOICE_TYPE <> 'ORDER' "+
		        "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' "+
		        "AND D.DOCTOR_CATEGORY_CODE LIKE '%' "+
		        "AND T.ACTIVE = '1' AND T.ORDER_ITEM_ACTIVE = '1' "+
		        "AND T.COMPUTE_DAILY_DATE IS NOT NULL AND T.COMPUTE_DAILY_DATE != '' "+
		        "AND (T.PAY_BY_CASH='Y' OR T.PAY_BY_AR='Y' OR T.PAY_BY_DOCTOR='Y' OR T.PAY_BY_PAYOR='Y' OR T.PAY_BY_CASH_AR='Y' ) "+
		        "AND T.IS_PAID = 'Y' " +
		        "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
    }
    private String payCreditDfOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE," +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE," +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.INVOICE_DATE, " +
                "'231014', "+
                "CASE WHEN (T.FIRST_DEPARTMENT_CODE = '' OR T.FIRST_DEPARTMENT_CODE IS NULL) " +
            		"THEN T.DOCTOR_DEPARTMENT_CODE ELSE T.FIRST_DEPARTMENT_CODE " +
            		"END AS FIRST_DEPARTMENT_CODE, " +
                "T.FIRST_LOCATION_CODE, " +
                "T.PATIENT_LOCATION_CODE, " +
                "T.RECEIPT_LOCATION_CODE, " +
                "T.ADMISSION_TYPE_CODE, " +
                "'DF', "+
                "T.NATIONALITY_CODE, "+
                "'THB', "+
                "T.AMOUNT_AFT_DISCOUNT, "+
                "'-', "+
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
        			"THEN 'DFCHECK' ELSE 'DFTRANS' " +
        			"END AS ACCOUNTING_TEMPLATE, " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
              
                "FROM TRN_DAILY T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN ORDER_ITEM OI ON (T.ORDER_ITEM_CODE = OI.CODE AND T.HOSPITAL_CODE = OI.HOSPITAL_CODE) "+

                "WHERE T.YYYY='"+this.YYYY+"' AND T.MM='"+this.MM+"' "+
		        "AND T.INVOICE_TYPE <> 'ORDER' "+
		        "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' "+
		        "AND D.DOCTOR_CATEGORY_CODE LIKE '%' "+
		        "AND T.ACTIVE = '1' AND T.ORDER_ITEM_ACTIVE = '1' "+
		        "AND T.COMPUTE_DAILY_DATE IS NOT NULL AND T.COMPUTE_DAILY_DATE != '' "+
		        "AND (T.PAY_BY_CASH='Y' OR T.PAY_BY_AR='Y' OR T.PAY_BY_DOCTOR='Y' OR T.PAY_BY_PAYOR='Y' OR T.PAY_BY_CASH_AR='Y' ) "+
		        "AND T.IS_PAID = 'Y' " +
		        "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
    }
    private String payDebitSharingOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE," +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE," +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.INVOICE_DATE, " +
                "'231014', "+
                "CASE WHEN (T.FIRST_DEPARTMENT_CODE = '' OR T.FIRST_DEPARTMENT_CODE IS NULL) " +
            		"THEN T.DOCTOR_DEPARTMENT_CODE ELSE T.FIRST_DEPARTMENT_CODE " +
            		"END AS FIRST_DEPARTMENT_CODE, " +
                "T.FIRST_LOCATION_CODE, " +
                "T.PATIENT_LOCATION_CODE, " +
                "T.RECEIPT_LOCATION_CODE, " +
                "T.ADMISSION_TYPE_CODE, " +
                "'DF', "+
                "T.NATIONALITY_CODE, "+
                "'THB', "+
                "T.AMOUNT_AFT_DISCOUNT-T.DR_AMT, "+
                "'+', "+
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
        			"THEN 'DFCHECK' ELSE 'DFTRANS' " +
        		"END AS ACCOUNTING_TEMPLATE, " +
	            "T.LINE_NO, "+ 
	            "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_DAILY T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN ORDER_ITEM OI ON (T.ORDER_ITEM_CODE = OI.CODE AND T.HOSPITAL_CODE = OI.HOSPITAL_CODE) "+
		        "WHERE T.YYYY='"+this.YYYY+"' AND T.MM='"+this.MM+"' "+
		        "AND T.INVOICE_TYPE <> 'ORDER' "+
		        "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' "+
		        "AND D.DOCTOR_CATEGORY_CODE LIKE '%' "+
		        "AND T.ACTIVE = '1' AND T.ORDER_ITEM_ACTIVE = '1' "+
		        "AND T.COMPUTE_DAILY_DATE IS NOT NULL AND T.COMPUTE_DAILY_DATE != '' "+
		        "AND (T.PAY_BY_CASH='Y' OR T.PAY_BY_AR='Y' OR T.PAY_BY_DOCTOR='Y' OR T.PAY_BY_PAYOR='Y' OR T.PAY_BY_CASH_AR='Y' ) "+
		        "AND T.IS_PAID = 'Y' " +
		        "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
    }
    private String payCreditSharingOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE," +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE," +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.INVOICE_DATE, " +
                "SUBSTRING(OI.ACCOUNT_CODE,1,3)+'2'+SUBSTRING(OI.ACCOUNT_CODE,5,6) AS ACCOUNT_CODE, "+
                "CASE WHEN (T.FIRST_DEPARTMENT_CODE = '' OR T.FIRST_DEPARTMENT_CODE IS NULL) " +
            		"THEN T.DOCTOR_DEPARTMENT_CODE ELSE T.FIRST_DEPARTMENT_CODE " +
            		"END AS FIRST_DEPARTMENT_CODE, " +
                "T.FIRST_LOCATION_CODE, " +
                "T.PATIENT_LOCATION_CODE, " +
                "T.RECEIPT_LOCATION_CODE, " +
                "T.ADMISSION_TYPE_CODE, " +
                "'DF', "+
                "T.NATIONALITY_CODE, "+
                "'THB', "+
                "T.AMOUNT_AFT_DISCOUNT-T.DR_AMT, "+
                "'-', "+
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
        			"THEN 'DFCHECK' ELSE 'DFTRANS' " +
        		"END AS ACCOUNTING_TEMPLATE, " +
	            "T.LINE_NO, "+ 
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_DAILY T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN ORDER_ITEM OI ON (T.ORDER_ITEM_CODE = OI.CODE AND T.HOSPITAL_CODE = OI.HOSPITAL_CODE) "+
		        "WHERE T.YYYY='"+this.YYYY+"' AND T.MM='"+this.MM+"' "+
		        "AND T.INVOICE_TYPE <> 'ORDER' "+
		        "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' "+
		        "AND D.DOCTOR_CATEGORY_CODE LIKE '%' "+
		        "AND T.ACTIVE = '1' AND T.ORDER_ITEM_ACTIVE = '1' "+
		        "AND T.COMPUTE_DAILY_DATE IS NOT NULL AND T.COMPUTE_DAILY_DATE != '' "+
		        "AND (T.PAY_BY_CASH='Y' OR T.PAY_BY_AR='Y' OR T.PAY_BY_DOCTOR='Y' OR T.PAY_BY_PAYOR='Y' OR T.PAY_BY_CASH_AR='Y' ) "+
		        "AND T.IS_PAID = 'Y' " +
		        "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
    }
    private String payDebitAbsorbDailyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.START_DATE, " +
                "'501301', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.HP402_ABSORB_AMOUNT+T.DF406_HOLD_AMOUNT, "+
	            "'+', " +
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
    				"THEN 'DFCHECK' ELSE 'DFTRANS' " +
    				"END AS ACCOUNTING_TEMPLATE, " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'DLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' "+
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
    }
    private String payCreditAbsorbDailyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.START_DATE, " +
                "'231014', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.HP402_ABSORB_AMOUNT+T.DF406_HOLD_AMOUNT, "+
	            "'-', " +
	            "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
	        		"THEN 'DFCHECK' ELSE 'DFTRANS' " +
	        		"END AS ACCOUNTING_TEMPLATE, " +	            
	        	//"'DFACCRUE', " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'DLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' "+
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
    }
    private String payDebitAbsorbMonthlyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT DISTINCT " +
                "T.HOSPITAL_CODE, " +
                "T.GUARANTEE_CODE, "+
                //"T.START_DATE, " +
                "'501301', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.HP402_ABSORB_AMOUNT+T.DF406_HOLD_AMOUNT, "+
	            "'+', " +
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
					"THEN 'DFCHECK' ELSE 'DFTRANS' " +
				"END AS ACCOUNTING_TEMPLATE, " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'MLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' "+
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
    }
    private String payCreditAbsorbMonthlyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT DISTINCT " +
                "T.HOSPITAL_CODE, " +
                "T.GUARANTEE_CODE, "+
                //"T.START_DATE, " +
                "'231014', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.HP402_ABSORB_AMOUNT+T.DF406_HOLD_AMOUNT, "+
	            "'-', " +
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
					"THEN 'DFCHECK' ELSE 'DFTRANS' " +
				"END AS ACCOUNTING_TEMPLATE, " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'MLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' "+
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
    }
    private String payDebitFixDailyOk(){
            return "INSERT INTO TEMP_GL(" +
    				"HOSPITAL_CODE, "+
    				"ACCOUNTING_DATE, "+
    			    "ACCOUNT_CODE, " +
    			    "FIRST_DEPARTMENT_CODE, "+
    			    "FIRST_LOCATION_CODE, " +
    			    "PATIENT_LOCATION_CODE, " +
    			    "RECEIPT_LOCATION_CODE, " +
    				"ADMISSION_TYPE_CODE, " +
    				"ACTIVITY, "+
    			    "NATIONALITY_CODE, " +
    			    "CURRENCY_CODE, " +
    			    "AMOUNT_AFT_DISCOUNT, " +
    			    "AMOUNT_SIGN, " +
    			    "ACCOUNTING_TEMPLATE, " +
    			    "LINE_NO, "+
    			    "MM, " +
    			    "YYYY) " +
                    
                    "SELECT " +
                    "T.HOSPITAL_CODE, " +
                    "T.START_DATE, " +
                    "'501302', "+
                    "D.DEPARTMENT_CODE, " +
                	"'', " + //FIRST LOCATION
                    "'', " + //PATIENT LOCATION
                    "'', " + //RECEIPT LOCATION
                    "'', " + //ADMISSION_TYPE
                    "'GR', "+ //ACTIVITY
                    "'', "+ //NATIONALITY
                    "'THB', "+ //CURRENCY
                    "T.GUARANTEE_FIX_AMOUNT+T.GUARANTEE_EXCLUDE_AMOUNT, "+
    	            "'+', " +
                    "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
    					"THEN 'DFCHECK' ELSE 'DFTRANS' " +
    				"END AS ACCOUNTING_TEMPLATE, " +
    	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                    "'"+this.MM+"', " +
                    "'"+this.YYYY+"' " +
                    
                    "FROM STP_GUARANTEE T " +
                    "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                    "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                    "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'DLY' " +
                    "AND D.ACTIVE = '1' AND T.ACTIVE = '1' "+
                    "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";

    }
    private String payCreditFixDailyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.START_DATE, " +
                "'231014', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.GUARANTEE_FIX_AMOUNT+T.GUARANTEE_EXCLUDE_AMOUNT, "+
	            "'-', " +
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
					"THEN 'DFCHECK' ELSE 'DFTRANS' " +
				"END AS ACCOUNTING_TEMPLATE, " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'DLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' "+
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
}
    private String payDebitFixMonthlyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.START_DATE, " +
                "'501302', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.GUARANTEE_FIX_AMOUNT+T.GUARANTEE_EXCLUDE_AMOUNT, "+
	            "'+', " +
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
					"THEN 'DFCHECK' ELSE 'DFTRANS' " +
				"END AS ACCOUNTING_TEMPLATE, " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'MLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' "+
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
    }
    private String payCreditFixMonthlyOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.START_DATE, " +
                "'231014', "+
                "D.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'GR', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.GUARANTEE_FIX_AMOUNT+T.GUARANTEE_EXCLUDE_AMOUNT, "+
	            "'-', " +
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
					"THEN 'DFCHECK' ELSE 'DFTRANS' " +
				"END AS ACCOUNTING_TEMPLATE, " +
	            "T.GUARANTEE_DR_CODE+':'+T.GUARANTEE_CODE, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM STP_GUARANTEE T " +
                "LEFT OUTER JOIN DOCTOR D ON (T.GUARANTEE_DR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND T.GUARANTEE_TYPE_CODE = 'MLY' " +
                "AND D.ACTIVE = '1' AND T.ACTIVE = '1' "+
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ')";
    }
    private String payDebitExpenseOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.DOC_DATE, " +
                "'231014', "+
                "T.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'AJ', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.AMOUNT, "+
	            "'+', " +
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
					"THEN 'DFCHECK' ELSE 'DFTRANS' " +
				"END AS ACCOUNTING_TEMPLATE, " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_EXPENSE_DETAIL T " +
                "LEFT OUTER JOIN EXPENSE E ON (T.EXPENSE_CODE = E.CODE AND T.HOSPITAL_CODE = E.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' AND T.EXPENSE_SIGN = '-1' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND E.GL_INTERFACE = 'Y' AND E.ADJUST_TYPE NOT IN ('DR','HP') " +
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ') AND E.ACTIVE = '1'";
    }
    private String payCreditExpenseOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.DOC_DATE, " +
                "T.EXPENSE_ACCOUNT_CODE, "+
                "T.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'AJ', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.AMOUNT, "+
	            "'-', " +
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
					"THEN 'DFCHECK' ELSE 'DFTRANS' " +
				"END AS ACCOUNTING_TEMPLATE, " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_EXPENSE_DETAIL T " +
                "LEFT OUTER JOIN EXPENSE E ON (T.EXPENSE_CODE = E.CODE AND T.HOSPITAL_CODE = E.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' AND T.EXPENSE_SIGN = '-1' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND E.GL_INTERFACE = 'Y' AND E.ADJUST_TYPE NOT IN ('DR','HP') " +
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ') AND E.ACTIVE = '1'";
    }
    private String payDebitAddOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.DOC_DATE, " +
                "T.EXPENSE_ACCOUNT_CODE, "+
                "T.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'AJ', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.AMOUNT, "+
	            "'+', " +
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
					"THEN 'DFCHECK' ELSE 'DFTRANS' " +
				"END AS ACCOUNTING_TEMPLATE, " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_EXPENSE_DETAIL T " +
                "LEFT OUTER JOIN EXPENSE E ON (T.EXPENSE_CODE = E.CODE AND T.HOSPITAL_CODE = E.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' AND T.EXPENSE_SIGN = '1' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND E.GL_INTERFACE = 'Y' AND E.ADJUST_TYPE NOT IN ('DR','HP') " +
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ') AND E.ACTIVE = '1'";
    }
    private String payCreditAddOk(){
        return "INSERT INTO TEMP_GL(" +
				"HOSPITAL_CODE, "+
				"ACCOUNTING_DATE, "+
			    "ACCOUNT_CODE, " +
			    "FIRST_DEPARTMENT_CODE, "+
			    "FIRST_LOCATION_CODE, " +
			    "PATIENT_LOCATION_CODE, " +
			    "RECEIPT_LOCATION_CODE, " +
				"ADMISSION_TYPE_CODE, " +
				"ACTIVITY, "+
			    "NATIONALITY_CODE, " +
			    "CURRENCY_CODE, " +
			    "AMOUNT_AFT_DISCOUNT, " +
			    "AMOUNT_SIGN, " +
			    "ACCOUNTING_TEMPLATE, " +
			    "LINE_NO, "+
			    "MM, " +
			    "YYYY) " +
                
                "SELECT " +
                "T.HOSPITAL_CODE, " +
                "T.DOC_DATE, " +
                "'231014', "+
                "T.DEPARTMENT_CODE, " +
            	"'', " + //FIRST LOCATION
                "'', " + //PATIENT LOCATION
                "'', " + //RECEIPT LOCATION
                "'', " + //ADMISSION_TYPE
                "'AJ', "+ //ACTIVITY
                "'', "+ //NATIONALITY
                "'THB', "+ //CURRENCY
                "T.AMOUNT, "+
	            "'-', " +
                "CASE WHEN D.PAYMENT_MODE_CODE = 'CQ' " +
					"THEN 'DFCHECK' ELSE 'DFTRANS' " +
				"END AS ACCOUNTING_TEMPLATE, " +
	            "T.LINE_NO, "+
                "'"+this.MM+"', " +
                "'"+this.YYYY+"' " +
                
                "FROM TRN_EXPENSE_DETAIL T " +
                "LEFT OUTER JOIN EXPENSE E ON (T.EXPENSE_CODE = E.CODE AND T.HOSPITAL_CODE = E.HOSPITAL_CODE) "+
                "LEFT OUTER JOIN DOCTOR D ON (T.DOCTOR_CODE = D.CODE AND T.HOSPITAL_CODE = D.HOSPITAL_CODE) "+
                "WHERE T.YYYY = '"+this.YYYY+"' AND T.MM = '"+this.MM+"' AND T.EXPENSE_SIGN = '1' "+
                "AND T.HOSPITAL_CODE = '"+this.Hospital_code+"' AND E.GL_INTERFACE = 'Y' AND E.ADJUST_TYPE NOT IN ('DR','HP') " +
                "AND D.PAYMENT_MODE_CODE IN ('B','CQ') AND E.ACTIVE = '1'";
    }

    private void insertIntoIntErpGl(){
        /*
        Hospital_Code	10000
        Seq_No	counter
        Accounting_DT	11/2/2008
        Accounting_Time	11.22.23
        Type	AC/GL
        Account	ACCOUNT_CODE
        Dept_ID	PATIENT_DEPARTMENT_CODE
        Product	ADMISSION TYPE CODE
        Monetary_Amount	AMOUNT_AFT_DISCOUNT
        Class_Fld	NATIONALITY_CODE
        Chartfield2	RECEIPT_LOCATION
        Chartfield3	PATIENT_LOCATION
        */
    	
    	String Accounting_Time = JDate.getHour() + "." + JDate.getMinutes() + "." + JDate.getSeconds();
        String Accounting_DT = JDate.getEndMonthDate(this.YYYY, this.MM)+ "/" + this.MM + "/" + this.YYYY;
    	String account_date_gl = "10/" + this.MM + "/" + this.YYYY;
    	String[][] d = null;
    	try{
            conn.setStatement();
            if(TypeData.equals("GL")){
            	Accounting_DT = "10/" + this.MM + "/" + this.YYYY;
        		d = conn.query("SELECT MAX(DISTINCT PAYMENT_TERM_DATE) FROM PAYMENT_MONTHLY WHERE HOSPITAL_CODE='"+this.Hospital_code+"'");
        		account_date_gl = d[0][0];
        		Accounting_DT = account_date_gl.substring(6, 8)+"/"+account_date_gl.substring(4, 6)+"/"+account_date_gl.substring(0, 4);
        	}
        }catch(Exception err){
            err.getMessage();
        }
        
        int Seq_No = 1;
        String sql = "SELECT HOSPITAL_CODE, ADMISSION_TYPE_CODE, PATIENT_DEPARTMENT_CODE, SUM(AMOUNT_AFT_DISCOUNT) as 'AMOUNT_AFT_DISCOUNT', " +
                "ACCOUNT_CODE, RECEIPT_LOCATION, PATIENT_LOCATION, FIRST_LOCATION, NATIONALITY_CODE, " +
                "AMOUNT_SIGN, CURRENCY_CODE, ACCOUNTING_TEMPLATE " +
                "FROM TEMP_GL WHERE ADMISSION_TYPE_CODE <>'U' AND MM='"+this.MM+"' AND YYYY='"+this.YYYY+"' AND HOSPITAL_CODE='"+this.Hospital_code+"' " +
                "GROUP BY HOSPITAL_CODE, ADMISSION_TYPE_CODE, PATIENT_DEPARTMENT_CODE, " +
                "ACCOUNT_CODE, RECEIPT_LOCATION, PATIENT_LOCATION, FIRST_LOCATION, NATIONALITY_CODE, AMOUNT_SIGN, " +
                "CURRENCY_CODE, ACCOUNTING_TEMPLATE";
        String sqlInsert = "";
        String Type = "";

        if(dbconn==null){
            dbconn = new DBConnection();
            dbconn.connectToLocal();
        }
        ResultSet rs = dbconn.executeQuery(sql);

        try{
            while(rs.next()){
                if("I".equalsIgnoreCase(rs.getString("ADMISSION_TYPE_CODE"))){
                    Type = "IPD";
                }else if("O".equalsIgnoreCase(rs.getString("ADMISSION_TYPE_CODE"))){
                    Type = "OPD";
                }else{
                    Type = "OPD";
                }

                sqlInsert = "INSERT INTO INT_ERP_GL(HOSPITAL_CODE, SEQ_NO, ACCOUNTING_DT, ACCOUNTING_TIME, " +
                        "TYPE, ACCOUNT, DEPT_ID, PRODUCT, MONETARY_AMOUNT, CURRENCY_CD, APPL_JRNL_ID, " +
                        "CLASS_FLD, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, YYYY, MM, AMOUNT_SIGN) " +
                        "VALUES('"+rs.getString("HOSPITAL_CODE") +"','"+ Seq_No +"','"+ Accounting_DT +"'," +
                        "'"+ Accounting_Time +"','"+TypeData+"','"+rs.getString("ACCOUNT_CODE")+"'," +
                        "'"+rs.getString("PATIENT_DEPARTMENT_CODE")+"','"+Type+"'," +
                        "'"+rs.getString("AMOUNT_AFT_DISCOUNT")+"','"+rs.getString("CURRENCY_CODE")+"'," +
                        "'"+rs.getString("ACCOUNTING_TEMPLATE")+"','"+rs.getString("NATIONALITY_CODE")+"'," +
                        "'"+rs.getString("FIRST_LOCATION")+"','"+rs.getString("RECEIPT_LOCATION")+"','"+Type+"'," +
                        "'"+rs.getString("PATIENT_LOCATION")+"','"+YYYY+"','"+MM+"','"+rs.getString("AMOUNT_SIGN")+"'); ";

                try{
                    //conn.setStatement();
                    conn.insert(sqlInsert);
                    conn.commitDB();
                }catch(Exception err){
                    conn.rollDB();
                    System.out.println("Insert : "+err);
                    err.getMessage();
                }
                Seq_No++;
            }
        }catch(Exception err){
            System.out.println(err.getMessage());
        }finally{
           conn.closeDB("");
        }
        count_insert = Seq_No-1;
        System.out.println(" Insert into INT_ERP_GL : " + count_insert + "");
    }
    public String processAccu(){
    	String message = "";
        String countdata[][] = null;
        this.TypeData = "AC";
        try{
            conn.setStatement();
            conn.insert(
            		"DELETE TEMP_GL WHERE MM = '"+this.MM+"' AND ACCOUNTING_TEMPLATE = 'DFACCRUE' AND " +
            		"YYYY='"+this.YYYY+"' AND HOSPITAL_CODE = '"+this.Hospital_code+"'"
            );
            //conn.insert("DELETE INT_ERP_GL WHERE TYPE='AC' AND YYYY='"+this.YYYY+"' AND HOSPITAL_CODE = '"+this.Hospital_code+"'");

            message = this.accuCreditAbsorbDailyOk();
            conn.insert(this.accuCreditAbsorbDailyOk());
            message = this.accuCreditAbsorbMonthlyOk();
            conn.insert(this.accuCreditAbsorbMonthlyOk());
            message = this.accuCreditExpenseOk();
            conn.insert(this.accuCreditExpenseOk());
            message = this.accuCreditDfOk();
            conn.insert(this.accuCreditDfOk());
            message = this.accuCreditFixDailyOk();
            conn.insert(this.accuCreditFixDailyOk());
            message = this.accuCreditFixMonthlyOk();
            conn.insert(this.accuCreditFixMonthlyOk());
            message = this.accuCreditSharingOk();
            conn.insert(this.accuCreditSharingOk());
            message = this.accuDebitAbsorbDailyOk();
            conn.insert(this.accuDebitAbsorbDailyOk());
            message = this.accuDebitAbsorbMonthlyOk();
            conn.insert(this.accuDebitAbsorbMonthlyOk());
            message = this.accuDebitExpenseOk();
            conn.insert(this.accuDebitExpenseOk());
            message = this.accuDebitDfOk();
            conn.insert(this.accuDebitDfOk());
            message = this.accuDebitFixDailyOk();
            conn.insert(this.accuDebitFixDailyOk());
            message = this.accuDebitFixMonthlyOk();
            conn.insert(this.accuDebitFixMonthlyOk());
            message = this.accuDebitSharingOk();
            conn.insert(this.accuDebitSharingOk());
            message = this.accuDebitAddOk();
            conn.insert(this.accuDebitAddOk());
            message = this.accuCreditAddOk();
            conn.insert(this.accuCreditAddOk());
            conn.commitDB();
            countdata = conn.query("SELECT count(*) as count_table FROM TEMP_GL WHERE HOSPITAL_CODE = '"+this.Hospital_code+"'");
            System.out.println("Data : " + countdata[0][0] + " record.");
            //insertIntoIntErpGl();
        }catch(Exception err){
            System.out.println(err);
            System.out.println("SQL Command Error : "+message);
            conn.rollDB();
        }finally{
            
            conn.closeDB("");
        }
        return countdata + "/" + count_insert;
    }
    public String processGlAccount(){
    	String message = "";
        String countdata[][] = null;
        this.TypeData = "GL";
        try{
            System.out.println("Hospital_code -> " + this.Hospital_code);
            System.out.println("YYYY -> " + this.YYYY);
            System.out.println("MM -> " + this.MM);
            conn.setStatement();
            conn.insert(
            		"DELETE TEMP_GL WHERE MM = '"+this.MM+"' AND ACCOUNTING_TEMPLATE <> 'DFACCRUE' AND " +
            		"YYYY='"+this.YYYY+"' AND HOSPITAL_CODE = '"+this.Hospital_code+"'"
            );
            //conn.insert("DELETE INT_ERP_GL WHERE TYPE='GL' AND YYYY='"+this.YYYY+"' AND HOSPITAL_CODE = '"+this.Hospital_code+"'");
            
            message = this.payCreditAbsorbDailyOk();
            conn.insert(this.payCreditAbsorbDailyOk());
            message = this.payCreditAbsorbMonthlyOk();
            conn.insert(this.payCreditAbsorbMonthlyOk());
            message = this.payCreditExpenseOk();
            conn.insert(this.payCreditExpenseOk());
            message = this.payDebitExpenseOk();
            conn.insert(this.payDebitExpenseOk());
            message = this.payCreditDfOk();
            conn.insert(this.payCreditDfOk());
            message = this.payCreditFixDailyOk();
            conn.insert(this.payCreditFixDailyOk());
            message = this.payCreditFixMonthlyOk();
            conn.insert(this.payCreditFixMonthlyOk());
            message = this.payCreditSharingOk();
            conn.insert(this.payCreditSharingOk());
            message = this.payDebitAbsorbDailyOk();
            conn.insert(this.payDebitAbsorbDailyOk());
            message = this.payDebitAbsorbMonthlyOk();
            conn.insert(this.payDebitAbsorbMonthlyOk());
            message = this.payCreditAddOk();
            conn.insert(this.payCreditAddOk());
            message = this.payDebitAddOk();
            conn.insert(this.payDebitAddOk());
            message = this.payDebitDfOk();
            conn.insert(this.payDebitDfOk());
            message = this.payDebitFixDailyOk();
            conn.insert(this.payDebitFixDailyOk());
            message = this.payDebitFixMonthlyOk();
            conn.insert(this.payDebitFixMonthlyOk());
            message = this.payDebitSharingOk();
            conn.insert(this.payDebitSharingOk());

            conn.commitDB();
            countdata = conn.query("SELECT count(*) as count_table FROM TEMP_GL WHERE HOSPITAL_CODE = '"+this.Hospital_code+"' AND YYYY='"+this.YYYY+"' AND MM='"+this.MM+"'");
            System.out.println("Data : " + countdata.length + " record.");
        }catch(Exception err){
            System.out.println(err);
            System.out.println("on sql "+message);
            conn.rollDB();
        }finally{
            //insertIntoIntErpGl();
            conn.closeDB("");
            return countdata + "/" + count_insert;
        }
    }

}